﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FWMS.DAO
{
    class Rukuguanli_yuancailiaoDao
    {
        //原材料查询全部
        public Bean.Rukuguanli_yuancailiaoBean rukuguanli_yuancailiaoDaoSelect()
        {
            DB.DBbase dbbase = new DB.DBbase();
            Bean.Rukuguanli_yuancailiaoBean bean = new FWMS.Bean.Rukuguanli_yuancailiaoBean();
            string sql = "";
            sql = "select cailiaoname from cailiaoguanli where cailiaotype = '原料'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //条件查询
        public Bean.Rukuguanli_yuancailiaoBean rukuguanli_yuancailiaoDaoSelectCondition(Bean.Rukuguanli_yuancailiaoBean bean)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select * from rukuguanli where ruku_type = '0'";
            sql = sql + " and ruku_no= '" + bean.Ruku_no + "'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //条件查询 库存管理用
        public Bean.Rukuguanli_yuancailiaoBean rukuguanli_yuancailiao_kucunuseDaoSelectCondition(Bean.Rukuguanli_yuancailiaoBean bean)//存在
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select yuanliaoruku_pinming,ruku_guige,ruku_danwei,ruku_danzhong,ruku_jianshu,ruku_zhongliang from rukuguanli where ruku_type = '0'";
            sql = sql + " and ruku_no= '" + bean.Ruku_no + "'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //入库原材料添加
        public bool rukuguanli_yuancailiaoDaoAdd(Bean.Rukuguanli_yuancailiaoBean bean)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = ""; // as user 
            sql = "delete from rukuguanli where ruku_no='" + bean.Ruku_no + "' and ruku_type='" + bean.Ruku_type + "'";
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            for (int i = 0; i < (bean.Selectresult.Length / 7); i++)
            {
                if ((bean.Selectresult[i, 0] == null) || ("".Equals(bean.Selectresult[i, 0])))
                {
                    continue;
                }
                sql = "insert into rukuguanli( ruku_type, ruku_daohuo, ruku_no, ruku_yanshou, ruku_fahuodanwei, yuanliaoruku_pinming, ruku_guige, ruku_danwei, ruku_danzhong, ruku_jianshu, ruku_zhongliang, ruku_beizhu ) values('" +
                bean.Ruku_type + "','" +
                bean.Ruku_daohuo + "','" +
                bean.Ruku_no + "','" +
                bean.Ruku_yanshou + "','" +
                bean.Ruku_fahuodanwei + "','" +
                bean.Selectresult[i, 0] + "','" +
                bean.Selectresult[i, 1] + "','" +
                bean.Selectresult[i, 2] + "','" +
                bean.Selectresult[i, 3] + "','" +
                bean.Selectresult[i, 4] + "','" +
                bean.Selectresult[i, 5] + "','" +
                bean.Selectresult[i, 6] + "')";
                if (!dbbase.ExecuteSQL(sql.Trim()))
                {
                    return false;
                }
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }//存在
        //库存管理用查询大于当前日期的原料数据
        public Bean.Rukuguanli_yuancailiaoBean rukuguanli_yuancailiao_kucunguanli_selectcondition_yanshoudate(string nowdate)
        {
            Bean.Rukuguanli_yuancailiaoBean bean = new FWMS.Bean.Rukuguanli_yuancailiaoBean();
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select yuanliaoruku_pinming,ruku_guige,ruku_danwei,sum(ruku_jianshu),sum(ruku_zhongliang) from rukuguanli where ruku_type = '0'";
            sql = sql + " and ruku_yanshou <= '" + nowdate + "'  group by yuanliaoruku_pinming,ruku_guige,ruku_danwei order by yuanliaoruku_pinming,ruku_guige,ruku_danwei ";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //查询库存最大时间
        public string rukuguanli_selectmaxkucundate(string type)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select max(thismonthsavedate)  from kucunguanli where cailiao_type = '" + type + "'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    return COMMAND.Command.toString(dbbase.rows[0, 0]);
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //判断是修改还是添加 减
        public bool kucunguanli_insertormodifyjian(Bean.Rukuguanli_yuancailiaoBean bean)//存在
        {
            string sql = "";
            Bean.KucunguanliBean kucunconditionbean = new FWMS.Bean.KucunguanliBean();
            for (int i = 0; i < bean.Selectresult.Length / 6; i++)
            {
                //查看库存中是否有原料入库材料 条件:品名+规格
                if ("".Equals(COMMAND.Command.toString(bean.Selectresult[i, 1])))
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '0' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and (guige = '' or guige is null)";
                }
                else
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '0' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and guige = '" + bean.Selectresult[i, 1].ToString().Trim() + "'";
                }
                object[,] result = (object[,])kucunguanli_select(sql);
                //查看库存中是否有原料入库输入的数据  原料入加查询:yuanliaoruku_pinming,ruku_guige,ruku_danwei,ruku_danzhong,ruku_jianshu,ruku_zhongliang
                if (result == null || "".Equals(COMMAND.Command.toString(result[0, 2])))
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Danwei = COMMAND.Command.toString(bean.Selectresult[i, 2]);
                    kucunconditionbean.Thismonthgongjin = COMMAND.Command.nulltozero(bean.Selectresult[i, 3]);
                    kucunconditionbean.Thismonthjianshu = COMMAND.Command.nulltozero(bean.Selectresult[i, 4]);
                    kucunconditionbean.Thismonthzhongliang = COMMAND.Command.nulltozero(bean.Selectresult[i, 5]);
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //添加
                    if (!kucunguanli_insert(kucunconditionbean))
                    {
                        return false;//INSERT失败
                    }
                }
                else//修改
                {   //resut:thismonthgongjin,thismonthjianshu,thismonthzhonglian
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    //库存中的数据减去原料入库添加的数据:公斤
                    kucunconditionbean.Thismonthgongjin = Math.Round((float.Parse(COMMAND.Command.nulltozero(result[0, 0])) - float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 3]))), 3).ToString().Trim();
                    //库存中的数据减去原料入库添加的数据:件数
                    kucunconditionbean.Thismonthjianshu = (int.Parse(COMMAND.Command.nulltozero(result[0, 1])) - int.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 4]))).ToString().Trim();
                    //库存中的数据减去原料入库添加的数据:重量
                    kucunconditionbean.Thismonthzhongliang = Math.Round((float.Parse(COMMAND.Command.nulltozero(result[0, 2])) - float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 5]))), 3).ToString().Trim();
                    //当时修改时间
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //修改
                    if (!kucunguanli_update(kucunconditionbean))
                    {
                        return false;//更新失败
                    }
                }
            }
            return true;
        }
        //判断是修改还是添加 加
        public bool kucunguanli_insertormodifyadd(Bean.Rukuguanli_yuancailiaoBean bean)//存在
        {
            string sql = "";
            Bean.KucunguanliBean kucunconditionbean = new FWMS.Bean.KucunguanliBean();
            for (int i = 0; i < bean.Selectresult.Length / 6; i++)
            {
                //查看库存中是否有时间段内出入库材料
                if ("".Equals(bean.Selectresult[i, 1].ToString().Trim()))
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '0' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and (guige = '' or guige is null)";
                }
                else
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '0' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and guige = '" + bean.Selectresult[i, 1].ToString().Trim() + "'";
                }
                object[,] result = (object[,])kucunguanli_select(sql);
                if (result == null || "".Equals(COMMAND.Command.toString(result[0, 2])))
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Danwei = COMMAND.Command.toString(bean.Selectresult[i, 2]);
                    kucunconditionbean.Thismonthgongjin = COMMAND.Command.nulltozero(bean.Selectresult[i, 3]);
                    kucunconditionbean.Thismonthjianshu = COMMAND.Command.nulltozero(bean.Selectresult[i, 4]);
                    kucunconditionbean.Thismonthzhongliang = COMMAND.Command.nulltozero(bean.Selectresult[i, 5]);
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //添加
                    if (!kucunguanli_insert(kucunconditionbean))
                    {
                        return false;//INSERT失败
                    }
                }
                else
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    //原料入库数据加库存存在数据:公斤
                    kucunconditionbean.Thismonthgongjin = Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 3])) + float.Parse(COMMAND.Command.nulltozero(result[0, 0])), 3).ToString().Trim();
                    //原料入库数据加库存存在数据:件数
                    kucunconditionbean.Thismonthjianshu = (int.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 4])) + int.Parse(COMMAND.Command.nulltozero(result[0, 1]))).ToString().Trim();
                    //原料入库数据加库存存在数据:重量
                    kucunconditionbean.Thismonthzhongliang = Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 5])) + float.Parse(COMMAND.Command.nulltozero(result[0, 2])), 3).ToString().Trim();
                    //更新时间为当前时间
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //修改
                    if (!kucunguanli_update(kucunconditionbean))
                    {
                        return false;//更新失败
                    }
                }
            }
            return true;
        }
        //更新库存
        public bool kucunguanli_update(object condition)//存在
        {
            string sql = "";
            Bean.KucunguanliBean kucunbean = (Bean.KucunguanliBean)condition;
            if ("".Equals(kucunbean.Guige.ToString().Trim()))
            {
                sql = "update kucunguanli set thismonthgongjin = '" + kucunbean.Thismonthgongjin +
                                      "',thismonthjianshu = '" + kucunbean.Thismonthjianshu +
                                      "', thismonthzhongliang = '" + kucunbean.Thismonthzhongliang +
                                      "', thismonthsavedate = '" + kucunbean.Thismonthsavedate +
                                      "'  where pinming = '" + kucunbean.Pinming.ToString().Trim() + "' and (guige ='' or guige is null) and cailiao_type= '0'";
            }
            else
            {
                sql = "update kucunguanli set thismonthgongjin = '" + kucunbean.Thismonthgongjin +
                                      "',thismonthjianshu = '" + kucunbean.Thismonthjianshu +
                                      "', thismonthzhongliang = '" + kucunbean.Thismonthzhongliang +
                                      "', thismonthsavedate = '" + kucunbean.Thismonthsavedate +
                                      "'  where pinming = '" + kucunbean.Pinming.ToString().Trim() + "' and guige= '" + kucunbean.Guige.ToString().Trim() + "' and cailiao_type= '0'";
            }
            //执行SQL
            if (!kucunguanli_addmodify(sql))
            {
                //更新失败；
                return false;
            }
            return true;
        }
        //库存添加数据
        public bool kucunguanli_insert(object condition)//存在
        {
            DB.DBbase dbbase = new DB.DBbase();

            Bean.KucunguanliBean bean = (Bean.KucunguanliBean)condition;
            string sql = ""; // as user 
            sql = "insert into kucunguanli( pinming, guige,danwei,thismonthgongjin, thismonthjianshu, thismonthzhongliang,thismonthsavedate, cailiao_type ) values('" +
                bean.Pinming + "','" +
                bean.Guige + "','" +
                bean.Danwei + "','" +
                bean.Thismonthgongjin + "','" +
                bean.Thismonthjianshu + "','" +
                bean.Thismonthzhongliang + "','" +
                bean.Thismonthsavedate + "','0' )";
            if (kucunguanli_addmodify(sql))
            {
                return true;//添加成功
            }
            else
            {
                return false;//添加失败
            }
        }
        //执行查询SQL
        private object kucunguanli_select(string sql)//存在
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    return dbbase.rows;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //执行添加修改SQL
        private bool kucunguanli_addmodify(string sql)//存在
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
